﻿using MySql.Data.MySqlClient;
using System.Data;
using System.Diagnostics;

namespace TeachlyQBank.DbPool
{
    public class MySqlConnectionAdapter : IDatabaseConnection
    {
        private readonly MySqlConnection _connection;
        private MySqlTransaction? _transaction;
        private string _lastError;                 //记录最后的一次错误
        private string _lastExecutedSql;     //记录最后执行的sql语句
        private long _lastExecutionTime;   //记录最后执行的sql语句花了多少时间

        public MySqlConnectionAdapter(string connectionString)
        {
            _connection = new MySqlConnection(connectionString);
            _lastError = "";
            _lastExecutedSql = "";
        }

        //开始事务
        public void BeginTransaction()
        {
            _transaction = _connection.BeginTransaction();
        }

        //提交事务
        public void CommitTransaction()
        {
            _transaction?.Commit();
        }

        //回滚事务
        public void RollbackTransaction()
        {
            _transaction?.Rollback();
        }

        //打开数据库
        public void Open()
        {
            _connection.Open();
        }

        //关闭数据库
        public void Close()
        {
            _connection.Close();
        }

        //内部绑定数据库语句的方法
        private void AttachParameters(MySqlCommand cmd, Dictionary<string, object>? parameters)
        {
            if (parameters == null) return;
            foreach (var param in parameters)
            {
                cmd.Parameters.AddWithValue(param.Key, param.Value);
            }
        }

        // 功能:执行查询语句
        // 参数:一个字符串做为数据库语句,如果需要绑定数据还需要传递一个字典做为绑定的列表
        // 执行成功返回一个DataSet,执行失败返回null,并更新错误信息
        public DataSet ExecuteQuery(string query, Dictionary<string, object>? parameters = null)
        {
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();

            try
            {
                _lastExecutedSql = query;
                DataSet ds = new DataSet();
                using (MySqlCommand cmd = new MySqlCommand(query, _connection))
                {
                    AttachParameters(cmd, parameters);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                    adapter.Fill(ds);
                }
                stopwatch.Stop();
                _lastExecutionTime = stopwatch.ElapsedMilliseconds;
                return ds;
            }
            catch (Exception ex)
            {
                stopwatch.Stop();
                _lastError = ex.Message;
#pragma warning disable CS8603
                return null;      //如果出现了错误这里就应该返回null,错误会在使用的时候进行处理
#pragma warning disable CS8603
            }
        }

        // 功能:执行非查询语句
        // 参数:一个字符串做为数据库语句,如果需要绑定数据还需要传递一个字典做为绑定的列表
        // 返回:执行成功这次查询语句影响的数据的行数,失败返回-1并更新错误信息
        public int ExecuteNonQuery(string query, Dictionary<string, object>? parameters = null)
        {
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();

            try
            {
                _lastExecutedSql = query;
                using (MySqlCommand cmd = new MySqlCommand(query, _connection))
                {
                    if (_transaction != null)
                    {
                        cmd.Transaction = _transaction;
                    }
                    AttachParameters(cmd, parameters);
                    int rowsAffected = cmd.ExecuteNonQuery();
                    _lastError = "";
                    stopwatch.Stop();
                    _lastExecutionTime = stopwatch.ElapsedMilliseconds;
                    return rowsAffected;
                }
            }
            catch (Exception ex)
            {
                stopwatch.Stop();
                _lastError = ex.Message;
                RollbackTransaction();
                return -1;
            }
        }

        // 功能:测试数据库连接
        // 返回:执行成功返回true,失败返回false并更新错误信息
        public bool TestConnection()
        {
            try
            {
                Open();
                Close();
                _lastError = "";
                return true;
            }
            catch (Exception ex)
            {
                _lastError = ex.Message;
                return false;
            }
        }

        // 功能:清除数据库资源
        public void Dispose()
        {
            _connection.Dispose();
        }

        // 如果出现了异常可以通过这个方法返回异常
        public string GetLastError()
        {
            return _lastError;
        }

        // 如果出现了异常可以通过这个方法返回最后执行的sql语句
        public string GetLastExecutedSql()
        {
            return _lastExecutedSql;
        }

        // 获取最后一次执行SQL语句花费的时间（以毫秒为单位）
        public long GetLastExecutionTime()
        {
            return _lastExecutionTime;
        }
    }
}

